import numpy as np
import os
import pandas as pd
import pymysql

def get_conn(host='127.0.0.1', port=3306, user='root', password='123456', db_name='school_project', charset='utf8'):
    """
    获取mysql的连接资源
    :param host: 主机地址 = 'yhni.cn' @type {String}
    :param port: 端口号 = 3306 @type {Int}
    :param user: 用户名 = 'python' @type {String}
    :param password: 密码 = '123456' @type {String}
    :param db_name: 所用数据名字 = 'PYTHON2020' @type {String}
    :param charset:设定字符集  = 'utf8' @type {String}
    :return: mysql connection
    """
    return pymysql.connect(host=host, port=port,
                           user=user, password=password,
                           db=db_name, charset=charset)


def query_data(conn, sql):
    """
    根据SQL语句查询数据并返回结果
    读操作
    :param conn: 数据库连接资源
    :param sql: SQL语句
    :return:list[dict]
    """
    try:
        cursor = conn.cursor(pymysql.cursors.DictCursor)  ##使每行数据以字典的类型返回
        cursor.execute(sql)
        return cursor.fetchall()
    finally:
        conn.close()


def insert_or_update_data(conn, sql):
    """
    根据SQL语句执行数据操纵并返回结果
    写操作
    :param conn: 数据库连接资源
    :param sql: SQL语句
    :return:不返回内容
    """
    try:
        cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()
    except Exception as error:
        print(error)
        conn.rollback()
    finally:
        conn.close()

if __name__ == '__main__':
    conn = get_conn()
    sql = "select * from a_zucc_school_project_users"
    res = query_data(conn, sql)
    data = pd.DataFrame(res)
    data_mean = data.mean()

    conn = get_conn()
    # sql = "update a_zucc_school_project_users set age = {0},sex={1},cp={2},trestbps={3},chol={4},fbs={5},restecg={6},thalach={7},exang={8},oldpeak={9},slope={10},ca={11},thal={12},target={13}  where id = 1".format(data_mean.age,data_mean.sex,data_mean.cp,data_mean.trestbps,data_mean.chol,data_mean.fbs,data_mean.restecg,data_mean.thalach,data_mean.exang,data_mean.oldpeak,data_mean.slope,data_mean.ca,data_mean.thal,data_mean.target)
    sql = "update a_zucc_school_project_users set age = {0},sex={1},cp={2},trestbps={3},chol={4},fbs={5},restecg={6},thalach={7},exang={8},oldpeak={9},slope={10},ca={11},thal={12},target={13}  where id = 1".format(data_mean.age,data_mean.sex,data_mean.cp,data_mean.trestbps,data_mean.chol,data_mean.fbs,data_mean.restecg,data_mean.thalach,data_mean.exang,data_mean.oldpeak,data_mean.slope,data_mean.ca,data_mean.thal,data_mean.target)
    insert_or_update_data(conn, sql)
